#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : yj_ch_first_demand_specify.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-10-16 17:15 
@Desc    : 有家&城宿合并首次正式邮件数据报表需求
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import df_util
from report_system.utils import date_util
from report_system.utils import num_util
from report_system.utils import excel_util
from report_system.report_specify.service_specify import district_refund_rate
from report_system.report_specify.service_specify import city_refund_rate
from report_system.report_specify.service_specify import work_refund_rate
from report_system.report_specify.service_specify import house_refund_rate


# todo  房屋维度↓↓↓房屋维度↓↓↓房屋维度↓↓↓房屋维度↓↓↓房屋维度↓↓↓房屋维度↓↓↓房屋维度↓↓↓房屋维度↓↓↓房屋维度↓↓↓房屋维度

def house_read_third_lodge_id():
    sql = """select 
    concat(lh.id,"") 房屋ID,
    ltl3.third_lodge_id 途家ID,
    ltl25.third_lodge_id 榛果ID,
    concat(aul.listing_id,'') 爱彼迎ID
    from youjia.lod_house lh
    left join  youjia.lodge l on lh.lodge_id = l.id 
    left join youjia.lod_third_lodge ltl3 on ltl3.lodge_id = l.id and ltl3.sales_channel_id = 3 
    left join youjia.lod_third_lodge ltl25 on ltl25.lodge_id = l.id and ltl25.sales_channel_id = 25 
    left join youjia.airbnb_upload_lodge aul on aul.lodge_id = l.id  and aul.is_delete = 0
    group by lh.id"""
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_third_lodge_id():
    """
    第三方渠道房屋id
    :return:
    """
    return house_read_third_lodge_id()


def house_read_situation(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    房屋维度 基础数据
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'

    dt = st if st == et else str(st) + "-->" + str(et)
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        case whs.own_type when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        whs.city_name 城市,
        ifnull(whs.work_name,'门店为空') 门店,
        concat(whs.house_id,"") 房屋ID,
        concat(whs.lodge_id,"") 房源ID,
        whs.title 房屋昵称,
        whs.shopowner 店长,
        whs.online_sale 线上运营,
        whs.offline_sale 线下管家,
        whs.invest_name 委托人,
        '{dt}' 日期
        from youjia_report.wh_house_static whs
         left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
         where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whs.own_type in {own_type}
        {sql_add}
        and whc.dt between '{st}' and '{et}'
        group by whs.house_id
        """.format(dt=dt, st=st, et=et, sql_add=sql_add, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_situation(st, et, own_type: tuple = (1, 2, 3), have_long=False):
    """
    房屋基础数据
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    df = house_read_situation(st=st, et=et, own_type=own_type, have_long=have_long)
    return df


def house_read_related(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    房源相关
    :param st:
    :param et:
    :param own_type:
    :param have_long:

    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        concat(whs.house_id,"") 房屋ID,
        date(whtn.online_time) 上线日期,
        whs.room_num 房型 ,
        sum(whc.online_stock) 在线天数,
        sum(if(whc.online_stock=1 and whc.rent_stock = 0,1,0)) 屏蔽天数,
        concat(round(sum(if(whc.online_stock=1 and whc.rent_stock = 1,1,0))/sum(whc.online_stock)*100,2),"%") 可售率
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_time_node whtn on whtn.house_id = whs.house_id
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whs.own_type in {own_type}
        {sql_add}
        and whc.dt between '{st}' and '{et}'
        group by whs.house_id
        """.format(st=st, et=et, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_related(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    df = house_read_related(st=st, et=et, own_type=own_type, have_long=have_long)
    return df


def house_read_check_rate(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    所有房屋的入住率
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    lase_year = '去年' if is_lase_year else ''
    sql = """select 
        concat(whc.house_id,"") 房屋ID,
        group_concat(distinct sc.name) 订单渠道,
        concat(whc.order_id,"") 有家订单编号,
        sum(whc.have_order) {lase_year}入住间夜,
        concat(round(sum(whc.have_order) /sum(if((whc.online_stock = 1 and rent_stock = 1) or whc.have_order=1,1,0)) * 100,2),"%") {lase_year}可售入住率,
        concat(round(target_check_rate/100,2),"%") 可售入住率月度目标,
        "-" 可售入住率距月度目标差,
        concat(round(sum(whc.have_order) /sum(if(whc.online_stock = 1 or whc.have_order=1,1,0)) * 100,2),"%") {lase_year}在线入住率 
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        left join youjia_common.sales_channel sc on sc.id = whc.order_channel
         where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whs.own_type in {own_type}
        {sql_add}
        and whc.dt between '{st}' and '{et}'
        group by whs.house_id
        order by sum(whc.have_order) /sum(if(whc.online_stock = 1 or whc.have_order=1,1,0)) desc
        """.format(st=st, et=et, sql_add=sql_add, lase_year=lase_year, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_check_rate(st, et, have_long: bool = False, own_type: tuple = (1, 2, 3)):
    """

    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return: 可售天数入住率 在线天数入住率 可售天数入住率排名 去年同期在线天数入住率 在线天数入住率同期对比 在线天数入住率目标完成情况

    """
    cur_df = house_read_check_rate(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    cur_df['可售入住率排名'] = cur_df.index + 1
    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = house_read_check_rate(st=last_st, et=last_et, have_long=have_long, is_lase_year=True, own_type=own_type)
    last_df = last_df[["房屋ID", "去年可售入住率"]]
    df = pd.merge(cur_df, last_df, how='left', on='房屋ID')
    df['可售入住率同期对比'] = df.apply(lambda row: num_util.contrast(row, '可售入住率', '去年可售入住率'), axis=1)
    df['可售入住率距月度目标差'] = df.apply(lambda row: num_util.difference(row, '可售入住率', '可售入住率月度目标'), axis=1)

    return df


def house_read_check_future14_rate(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    所有房屋的入住率
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    lase_year = '去年' if is_lase_year else ''
    sql = """select 
        concat(whc.house_id,"") 房屋ID,
        concat(round(sum(whc.have_order) /sum(if((whc.online_stock = 1 and rent_stock = 1) or whc.have_order=1,1,0)) * 100,2),"%") 未来14天可售入住率
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        left join youjia_common.sales_channel sc on sc.id = whc.order_channel
         where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whs.own_type in {own_type}
        {sql_add}
        and whc.dt between '{st}' and '{et}'
        group by whs.house_id
        order by sum(whc.have_order) /sum(if(whc.online_stock = 1 or whc.have_order=1,1,0)) desc
        """.format(st=st, et=et, sql_add=sql_add, own_type=own_type, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_check_future14_rate(st, et, have_long: bool = False, own_type: tuple = (1, 2, 3)):
    """

    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return: 未来十四天入住率

    """
    et = date_util.date_add(et, days=14)
    df = house_read_check_future14_rate(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    return df


def house_read_adr(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    lase_year = '去年' if is_lase_year else ''

    sql = """select 
    concat(whc.house_id,"") 房屋ID,
    round(sum(income) / sum(whc.have_order)/100,2) {lase_year}ADR,
    round(target_adr/100,2) ADR月度目标,
    round((sum(income) / sum(whc.have_order) - target_adr)/100,2) ADR据月度目标差,
    round(sum(ifnull(whc.airbnb_avg_price,0))/sum(if(whc.airbnb_avg_price<>0 and whc.airbnb_avg_price is not null ,1,0)),2) 市场价格
    from youjia_report.wh_house_static whs
    left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
   where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whs.own_type in {own_type}
        {sql_add}
        and whc.dt between '{st}' and '{et}'
        group by whs.house_id
    """.format(st=st, et=et, sql_add=sql_add, own_type=own_type, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_adr(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    cur_df = house_read_adr(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = house_read_adr(st=last_st, et=last_et, have_long=have_long, is_lase_year=True, own_type=own_type)
    last_df = last_df[["房屋ID", "去年ADR"]]
    df = pd.merge(cur_df, last_df, how='left', on='房屋ID')
    df['ADR同期对比差额'] = df.apply(lambda row: row['ADR'] - row['去年ADR'], axis=1)
    return df


def house_read_gmv(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """
    GMV	租金完成度排名	租金完成度
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'

    sql = """select 
    concat(whc.house_id,"") 房屋ID,
    round(sum(income+compensate_income+other_income)/100 ,2)  GMV【不包含刷单】,
    round(target_gmv/100,2) GMV目标,
    round((sum(income+compensate_income+other_income) - target_gmv)/100,2) GMV目标差,
    0 租金完成度排名,
    concat(round(sum(income+compensate_income)/sum(rent_day)*100,2),"%") 租金完成度
    from youjia_report.wh_house_static whs
    left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
     where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whs.own_type in {own_type}
        {sql_add}
        and whc.dt between '{st}' and '{et}'
        group by whs.house_id
    order by sum(income+compensate_income)/sum(rent_day) desc
    """.format(st=st, et=et, sql_add=sql_add, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_gmv(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    GMV	租金完成度排名	租金完成度
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    df = house_read_gmv(st=st, et=et, have_long=have_long, own_type=own_type)
    df['租金完成度排名'] = df.index + 1
    return df


def house_read_revpar(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year=False):
    """
    GMV	租金完成度排名	租金完成度
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :param is_lase_year:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    lase_year = '去年' if is_lase_year else ''

    sql = """select 
    concat(whc.house_id,"") 房屋ID,
    round(sum(income+compensate_income)/sum(if((whc.online_stock=1 and whc.rent_stock=1) or whc.have_order = 1,1,0))/100,2) {lase_year}REVPAR
    from youjia_report.wh_house_static whs
    left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
    where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
    and whs.own_type in {own_type}
    {sql_add}
    and whc.dt between '{st}' and '{et}'
    group by whs.house_id
    order by sum(income+compensate_income)/sum(rent_day) desc
    """.format(st=st, et=et, sql_add=sql_add, lase_year=lase_year, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_revpar(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    GMV	租金完成度排名	租金完成度
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    cur_df = house_read_revpar(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = house_read_revpar(st=last_st, et=last_et, have_long=have_long, is_lase_year=True, own_type=own_type)
    df = pd.merge(cur_df, last_df, how='left', on='房屋ID')
    df['REVPAR同期对比差额'] = df.apply(lambda row: row['REVPAR'] - row['去年REVPAR'], axis=1)
    return df


def house_df_order_about(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """
    房屋ID	取消订单收入	退款率
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    df = house_df_create_orders_num(st=st, et=et, own_type=own_type, have_long=have_long)

    house_refund_rate_df = house_refund_rate(st=st, et=et, own_type=own_type, have_long=have_long, is_int=False)
    df = pd.merge(df, house_refund_rate_df, how='left')
    return df


def house_read_channel_order_rate(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """
    离店订单占比-airbnb	离店订单占比-途家	离店订单占比-榛果	离店订单占比-其他
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    sql = """select 
    concat(whs.house_id,"") 房屋ID,
    concat(round(ifnull(count(distinct if(order_channel = 20,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-Airbnb`,
    concat(round(ifnull(count(distinct if(order_channel = 3,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-途家`,
    concat(round(ifnull(count(distinct if(order_channel = 25,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-榛果`,
    concat(round(ifnull(count(distinct if(order_channel not in (3,20,25),order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-其他` 
    from youjia_report.wh_house_static whs
    left join youjia_report.wh_house_calendar whc on whs.house_id = whc.house_id
    where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
    and whs.own_type in {own_type}
    {sql_add}
    and whc.dt between '{st}' and '{et}'
    group by whs.house_id""".format(st=st, et=et, sql_add=sql_add, own_type=own_type)

    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_channel_order_rate(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    渠道占比
    离店订单占比-airbnb	离店订单占比-途家	离店订单占比-榛果	离店订单占比-其他
    :param st:
    :param et:
    :param have_long:
    :return:
    """
    return house_read_channel_order_rate(st=st, et=et, have_long=have_long, own_type=own_type)


def house_read_create_orders_num(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """

    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)

    sql = """select 
             concat(whs.house_id,"") 房屋ID,
             round(sum(whc.compensate_income)/100,2) 取消订单收入
             from youjia_report.wh_house_static whs
             left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
             where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
             and whc.dt between '{st}' and '{et}'
             and whs.own_type in {own_type}
             {sql_add}
             group by whs.house_id
        """.format(st=st, et=et, sql_add=sql_add, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_create_orders_num(st, et, own_type, have_long):
    """
    预定订单	预定订单间夜
    :param st:
    :param et:
    :param have_long:
    :return:
    """
    return house_read_create_orders_num(st=st, et=et, own_type=own_type, have_long=have_long)


def house_read_phone(st, et):
    """
    电话接起率
    :param st:
    :param et:
    :return:
    """
    df = pd.DataFrame([{"房屋ID": "", "来电次数": "", "接起电话次数": "", "电话接起率": ""}])
    df = df_util.df_set_index(df, '房屋ID')
    return df


def house_df_phone(st, et):
    """
    电话接起率
    :param st:
    :param et:
    :return:
    """
    return house_read_phone(st=st, et=et)


def house_read_channel(st, et, own_type: tuple = (1, 2, 3), channel_ids: list = [3, 20, 25]):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)
    sql = """SELECT 
    concat(whs.house_id,"") 房屋ID,
    concat(round(ifnull(sum(if(wlc.is_distribution = 1,ifnull(wlc.third_state,0),0))/sum(ifnull(wlc.is_distribution,0)),0)*100,2),"%") 直连在线率,
    concat(round(ifnull(sum(ifnull(wlc.is_distribution,0))/sum(whc.online_stock),0)*100,2),"%") 渠道直连率,
    if(wlc.third_state = 1,"是","否") 是否在线,
    if(wlc.is_distribution = 1,"是","否") 是否直连
    
    FROM youjia_report.wh_house_static whs 
    left join youjia_report.wh_lodge_calendar wlc on whs.house_id = wlc.house_id
    left join youjia_report.wh_house_calendar whc on wlc.house_id = whc.house_id and wlc.dt = whc.dt
    where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
    and wlc.dt between '{st}' and '{et}'
    and whc.dt between '{st}' and '{et}'
    and wlc.house_id = whc.house_id 
    and wlc.dt = whc.dt
    and wlc.channel_id in {channel_ids}
    and whs.own_type in {own_type}
    group by whs.house_id ;
    """.format(st=st, et=et, channel_ids=channel_ids, own_type=own_type)
    # df = pd.DataFrame([{"房屋ID": "", "渠道在线率": "", "渠道直连率": "", "离店订单数占总订单数比例": ""}])
    # df = df_util.df_set_index(df, '房屋ID')
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_channel(st, et, channel_ids=[3, 20, 25], own_type=(1, 2, 3)):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param channel_ids:
    :param own_type:
    :return:
    """
    return house_read_channel(st=st, et=et, channel_ids=channel_ids, own_type=own_type)


def house_read_brush(st, et, own_type=(1, 2, 3), have_long: bool = False, channel_ids=[3, 20, 25]):
    """
    刷单入住情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :param channel_ids:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)

    sql = """select 
            concat(whc.house_id,"") 房屋ID,
            count(distinct if(brush_channel in {channel_ids},brush_order_id,null)) 刷单数,
    
            round(sum(if(brush_channel in {channel_ids},brush_income,0))/sum(if(brush_channel in {channel_ids},brush_night,0))/100,2) 刷单ADR,
            sum(if(brush_channel in {channel_ids},brush_night,0))/ count(distinct if(brush_channel in {channel_ids},brush_order_id,null)) 刷单订单平均间夜量,
            concat(round(sum(if(order_channel in {channel_ids},have_order,0)+ if(brush_channel in {channel_ids},brush_night,0))/ sum(if(whc.online_stock=1 and whc.rent_stock = 1,1,0))*100,2),"%") 入住率（含刷单）
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whs.own_type in {own_type}
            {sql_add}
            and whc.dt between '{st}' and '{et}'
            group by whs.house_id
        """.format(st=st, et=et, channel_ids=channel_ids, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.con_youjia)


def house_df_brush(st, et, have_long: bool = False, channel_ids=[3, 20, 25], own_type=(1, 2, 3)):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param have_long:
    :param channel_ids:
    :param own_type:
    :return:
    """
    return house_read_brush(st=st, et=et, have_long=have_long, channel_ids=channel_ids, own_type=own_type)


# todo  城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度

# todo  城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度

# todo  城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度

# todo  城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度↓↓↓城市维度

def city_read_related(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False, is_lase_year=False):
    """
    房源相关
    :param st:
    :param et:
    :param own_type:
    :param have_long:

    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    lase_year = '去年' if is_lase_year else ''
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        COALESCE(whs.city_name,'全国')  城市,
         case group_concat(distinct whs.own_type order by whs.own_type)
             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        if('{st}'='{et}','{st}',concat('{st}','-->','{et}')) 日期, 
        count(distinct if(date(whtn.online_time) between '{st}' and '{et}',whs.house_id,null)) 新增上线房屋,
        count(distinct if(date(whtn.offline_time) between '{st}' and '{et}',whs.house_id,null)) 新增下线房屋,
        -- count(distinct if(whs.house_state = 100,whs.house_id,null)) 当前在线房屋,
        count(distinct if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),whs.house_id,null)) {lase_year}同期在线房屋,
        sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) 在线间夜,
        sum(if(whc.online_stock=1 and whc.rent_stock = 0,1,0)) 屏蔽间夜,
        concat(round(sum(if(whc.online_stock=1 and whc.rent_stock = 1,1,0))/sum(whc.online_stock)*100,2),"%") 可售率 
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_time_node whtn on whtn.house_id = whs.house_id
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whs.own_type in {own_type}
        {sql_add}
        and whc.dt between '{st}' and '{et}'
        and whs.work_name not like '%特殊门店'
        group by whs.own_type,whs.city_name
        with rollup
        """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_related(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    df = city_read_related(st=st, et=et, own_type=own_type, have_long=have_long)

    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    lase_year_df = city_read_related(st=last_st, et=last_et, own_type=own_type, have_long=have_long, is_lase_year=True)
    lase_year_df = lase_year_df[["公司", "城市", "去年同期在线房屋"]]
    df = pd.merge(df, lase_year_df, how='left', on=["公司", "城市"])
    df = df_util.df_sort(df, '同期在线房屋')
    df = df[["城市", "公司", "日期", "新增上线房屋", "新增下线房屋", "去年同期在线房屋", "同期在线房屋", "在线间夜", "屏蔽间夜", "可售率"]]
    return df


def city_read_check(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False, is_lase_year=False):
    """
    房源相关
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:

    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """   select 
                case group_concat(distinct A.own_type order by A.own_type)
                             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
                COALESCE(A.城市,'全国')  城市,
                sum(A.入住间夜) 入住间夜,
                concat(round(sum(A.入住间夜)/sum(A.可售间夜)*100,2),"%") {lase_year}可售入住率,
                concat(round(avg(A.月度可售入住率目标),2),"%") 月度可售入住率目标,
                concat(round(sum(A.入住间夜)/sum(A.可售间夜)*100-avg(A.月度可售入住率目标) ,2),"%") 可售入住率距月度目标差,
                sum(A.入住间夜)/sum(A.可售间夜) 可售入住率排名,
                concat(round(sum(A.入住间夜)/sum(A.在线间夜)*100,2),"%") 在线入住率,
                concat(round(sum(if(A.居室 =1, A.入住间夜,0))/sum(if(A.居室 =1, A.可售间夜,0))*100 ,2),"%") 一居可售入住率,
                concat(round(sum(if(A.居室 =2, A.入住间夜,0))/sum(if(A.居室 =2, A.可售间夜,0))*100 ,2),"%") 二居可售入住率,
                concat(round(sum(if(A.居室 =3, A.入住间夜,0))/sum(if(A.居室 =3, A.可售间夜,0))*100 ,2),"%") 三居可售入住率,
                concat(round(sum(if(A.居室 >3, A.入住间夜,0))/sum(if(A.居室 >3, A.可售间夜,0))*100 ,2),"%") 三居以上可售入住率
             from (select 
                    whs.house_id,
                    whs.own_type,
                    whs.room_num 居室,
                    whs.city_name 城市,
                    whs.district_name 大区, 
                    sum(whc.have_order)  入住间夜,
                    round(target_check_rate/100,2)  月度可售入住率目标,
                    sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) 在线间夜,
                    sum(if((whc.online_stock = 1 and rent_stock = 1) or whc.have_order=1,1,0)) 可售间夜 
                    from youjia_report.wh_house_static whs
                    left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
                    where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
                    and whc.dt between '{st}' and '{et}'
                    and whs.own_type in {own_type}
                    {sql_add}
                    and whs.work_name not like '%特殊门店'
                    group by whs.house_id
                    ) A
            group by  A.own_type,A.城市
            with rollup
        """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_check(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return: 入住率
    """

    cur_df = city_read_check(st=st, et=et, own_type=own_type, have_long=have_long, is_lase_year=False)
    df_total = cur_df[cur_df.城市 == '全国'].copy()
    # df_total['可售入住率排名'] = '-'
    df_total['可售入住率排名'] = df_total.apply(lambda row: "-", axis=1)

    df_other = cur_df[cur_df.城市 != '全国'].copy()
    df_other = df_util.df_set_index(df_other, ['城市', '公司'])
    df_other = df_util.df_sort(df_other, ['可售入住率排名'])
    df_other = df_util.df_reset_index(df_other)
    df_other['可售入住率排名'] = df_other.index + 1
    cur_df = pd.concat([df_total, df_other])

    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = city_read_check(st=last_st, et=last_et, have_long=have_long, is_lase_year=True)
    last_df = last_df[['城市', '公司', '去年可售入住率']]

    df = pd.merge(last_df, cur_df, how='right', on=['城市', '公司'])

    df['可售入住率同期对比'] = df.apply(lambda row: num_util.contrast(row, '可售入住率', '去年可售入住率'), axis=1)

    return df


def city_read_check_future14(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False, is_lase_year=False):
    """
    房源相关
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:

    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        COALESCE(whs.city_name,'全国')  城市,
         case group_concat(distinct whs.own_type order by whs.own_type)
             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        concat(round(sum(whc.have_order) /sum(if((whc.online_stock = 1 and rent_stock = 1) or whc.have_order=1,1,0)) * 100,2),"%")  未来14天可售入住率
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whc.dt between '{st}' and '{et}'
        and whs.own_type in {own_type}
        {sql_add}
        and whs.work_name not like '%特殊门店'
        group by whs.own_type,whs.city_name
        with rollup
        """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_check_future14(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return: 入住率
    """
    et = date_util.date_add(et, days=14)

    return city_read_check_future14(st=st, et=et, own_type=own_type, have_long=have_long, is_lase_year=False)


def city_read_adr(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :param is_lase_year:
    :return:
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            COALESCE(whs.city_name,'全国')  城市,
            case group_concat(distinct whs.own_type order by whs.own_type)
            when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            round(sum(ifnull(whc.airbnb_avg_price,0))/sum(if(whc.airbnb_avg_price<>0 and whc.airbnb_avg_price is not null ,1,0)),2) 市场价格,
            round(sum(income) / sum(whc.have_order)/100,2) {lase_year}ADR,
            '' ADR同期对比差额,
            round(target_adr/100,2) 月度ADR目标,
            round((sum(income) / sum(whc.have_order) - target_adr)/100,2) ADR距月度目标差,
            round(sum(if(whs.room_num=1,ifnull(income,0),0))/sum(if(whs.room_num=1,whc.have_order,0))/100,2)  一居ADR ,
            round(sum(if(whs.room_num=2,ifnull(income,0),0))/sum(if(whs.room_num=2,whc.have_order,0))/100,2)  二居ADR ,
            round(sum(if(whs.room_num=3,ifnull(income,0),0))/sum(if(whs.room_num=3,whc.have_order,0))/100,2)  三居ADR ,
            round(sum(if(whs.room_num>3,ifnull(income,0),0))/sum(if(whs.room_num>3,whc.have_order,0))/100,2)  三居以上ADR 
            
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt between '{st}' and '{et}'
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name not like '%特殊门店'
            group by whs.own_type,whs.city_name
            with rollup
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_adr(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    cur_df = city_read_adr(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = city_read_adr(st=last_st, et=last_et, have_long=have_long, is_lase_year=True, own_type=own_type)
    last_df = last_df[['城市', '公司', '去年ADR']]
    df = pd.merge(cur_df, last_df, how='left', on=['城市', '公司'])
    df['ADR同期对比差额'] = df.apply(lambda row: row['ADR'] - row['去年ADR'], axis=1)
    return df


def city_read_adr_future14(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            COALESCE(whs.city_name,'全国')  城市,
            case group_concat(distinct whs.own_type order by whs.own_type)
            when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            round(sum(income) / sum(whc.have_order)/100,2) 未来14天平均ADR 
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt between '{st}' and '{et}'
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name not like '%特殊门店'
            group by whs.own_type,whs.city_name
            with rollup
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_adr_future14(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :return:
    """
    et = date_util.date_add(days=14, dt=et)
    return city_read_adr_future14(st=st, et=et, have_long=have_long, own_type=own_type)


def city_read_revpar(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :param is_lase_year:
    :return:
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            COALESCE(whs.city_name,'全国')  城市,
            case group_concat(distinct whs.own_type order by whs.own_type)
            when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            round(sum(income+compensate_income)/sum(if((whc.online_stock=1 and whc.rent_stock=1) or whc.have_order = 1,1,0))/100,2) {lase_year}RevPar
            
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt between '{st}' and '{et}'
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name not like '%特殊门店'
            group by whs.own_type,whs.city_name
            with rollup
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_revpar(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    市场价格	revpar	RevPar	去年同期Revpar	RevPar同期比差额

    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    cur_df = city_read_revpar(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = city_read_revpar(st=last_st, et=last_et, have_long=have_long, is_lase_year=True, own_type=own_type)
    last_df = last_df[['城市', '公司', '去年RevPar']]
    df = pd.merge(cur_df, last_df, how='left', on=['城市', '公司'])
    df['RevPar同期对比差额'] = df.apply(lambda row: row['RevPar'] - row['去年RevPar'], axis=1)
    return df


def city_read_gmv(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    GMV	套均GMV	套均租金完成度排名	套均租金完成度	目标套均租金完成度
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:
    :return:
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select A.城市,A.公司,A.GMV【不包含刷单】,A.套均GMV【不包含刷单】,A.GMV目标,A.GMV目标差,A.套均租金完成度排名,A.套均租金完成度
                from (select 
                COALESCE(whs.city_name,'全国')  城市,
                case group_concat(distinct whs.own_type order by whs.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
                round(sum(income+compensate_income+other_income)/100,2) GMV【不包含刷单】,
                round(sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)/100,2) 套均GMV【不包含刷单】,
                round(avg(target_gmv)/100,2)  GMV目标,
                round((sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)-avg(target_gmv))/100,2) GMV目标差,
                sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)/(sum(rent_day)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)) 套均租金完成度排名, 
                concat(round(sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)/(sum(rent_day)/sum(whc.online_stock)*(datediff('{et}','{st}')+1))*100,2),"%") 套均租金完成度
                -- concat(round(sum(income+compensate_income)/count(distinct whs.house_id)/(sum(rent_day)/count(distinct whs.house_id))*100,2),"%") 套均租金完成度
    
                
                from youjia_report.wh_house_static whs
                left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
                where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
                and whc.dt between '{st}' and '{et}'
                and whs.own_type in {own_type}
                {sql_add}
                and whs.work_name not like '%特殊门店'
                group by whs.own_type,whs.city_name
                with rollup) A order by 套均租金完成度排名 desc 
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_gmv(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    GMV	套均GMV	套均租金完成度排名	套均租金完成度	目标套均租金完成度

    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    df = city_read_gmv(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    df['套均租金完成度排名'] = df.index + 1

    df_total = df[df.城市 == '全国'].copy()
    # df_total['套均租金完成度排名'] = 0
    df_total['套均租金完成度排名'] = df_total.apply(lambda row: '-', axis=1)

    df_other = df[df.城市 != '全国'].copy()
    df_other = df_util.df_set_index(df_other, ['公司', '城市'])
    df_other = df_util.df_sort(df_other, ['套均租金完成度排名'], ascending=True)
    df_other = df_util.df_reset_index(df_other)
    df_other['套均租金完成度排名'] = df_other.index + 1
    df = pd.concat([df_total, df_other], sort=False)
    return df


def city_read_create_orders_num(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """

    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'

    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)

    sql = """select 
             case group_concat(distinct whs.own_type order by whs.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
             COALESCE(whs.city_name,'全国')  城市,
             round(sum(whc.compensate_income)/100,2) 取消订单收入
             from youjia_report.wh_house_static whs
             left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
             where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
             and whc.dt between '{st}' and '{et}'
             and whs.own_type in {own_type}
             {sql_add}
             and whs.work_name not like '%特殊门店'
             group by whs.own_type,whs.city_name
             with rollup
        """.format(st=st, et=et, sql_add=sql_add, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_create_orders_num(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """

    :param st:
    :param et:
    :param have_long:
    :return:
    """
    df = city_read_create_orders_num(st=st, et=et, have_long=have_long)
    city_refund_rate_df = city_refund_rate(st=st, et=et, own_type=own_type, is_int=False)
    city_refund_rate_df = df_util.df_reset_index(city_refund_rate_df)
    df = pd.merge(df, city_refund_rate_df, how='left', on=['公司', '城市'])
    df = df[['公司', '城市', '取消订单收入', '退款率']]
    # city_refund_rate_df = df_util.df_reset_index(city_refund_rate_df)
    # df = pd.merge(df, city_refund_rate_df, how='left')

    return df


def city_read_net_profit(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """

    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    sql_add = "" if have_long else 'and bo.order_type <> 3'

    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    net_profits = [{"公司": "-", "城市": "-", "净利-公司": "-", "套均净利-公司": "-", "净利-投资人": "-", "套均净利-投资人": "-"}]

    return pd.DataFrame(net_profits)


def city_df_net_profit(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """
        净利
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """

    return city_read_net_profit(st=st, et=et, own_type=own_type, have_long=have_long)


def city_read_channel_order_rate(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    离店订单占比-airbnb	离店订单占比-途家	离店订单占比-榛果	离店订单占比-其他
    :param st:
    :param et:
    :param have_long:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
    COALESCE(whs.city_name,'全国')  城市,
    case group_concat(distinct whs.own_type order by whs.own_type)
        when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
    concat(round(ifnull(count(distinct if(order_channel = 20,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-Airbnb`,
    concat(round(ifnull(count(distinct if(order_channel = 3,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-途家`,
    concat(round(ifnull(count(distinct if(order_channel = 25,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-榛果`,
    concat(round(ifnull(count(distinct if(order_channel not in (3,20,25),order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-其他` 
    from youjia_report.wh_house_static whs
    left join youjia_report.wh_house_calendar whc on whs.house_id = whc.house_id
    where  whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
     and whc.dt between '{st}' and '{et}'
     and whs.own_type in {own_type}
    {sql_add}
    and whs.work_name not like '%特殊门店'
    group by whs.own_type,whs.city_name
    with rollup""".format(st=st, et=et, sql_add=sql_add, own_type=own_type)

    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_channel_order_rate(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    渠道占比
    离店订单占比-airbnb	离店订单占比-途家	离店订单占比-榛果	离店订单占比-其他
    :param st:
    :param et:
    :param have_long:
    :return:
    """
    return city_read_channel_order_rate(st=st, et=et, have_long=have_long, own_type=own_type)


def city_read_phone(st, et):
    """
    电话接起率
    :param st:
    :param et:
    :return:
    """
    df = pd.DataFrame([{"城市": "全国", "公司": "所有公司", "来电次数": "", "接起电话次数": "", "电话接起率": ""}])
    df = df_util.df_set_index(df, ['公司', '城市'])
    return df


def city_df_phone(st, et):
    """
    电话接起率
    :param st:
    :param et:
    :return:
    """
    return city_read_phone(st=st, et=et)


def city_read_channel(st, et, own_type: tuple = (1, 2, 3), channel_ids: list = [3, 20, 25]):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)
    sql = """SELECT 
	case group_concat(distinct whs.own_type order by whs.own_type)
            when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
    COALESCE(whs.city_name,'全国')  城市,
    concat(round(ifnull(sum(if(wlc.is_distribution = 1,ifnull(wlc.third_state,0),0))/sum(ifnull(wlc.is_distribution,0)),0)*100,2),"%") 直连在线率,
    concat(round(ifnull(sum(ifnull(wlc.is_distribution,0))/sum(whc.online_stock),0)*100,2),"%") 渠道直连率 
    
    FROM youjia_report.wh_house_static whs 
    left join youjia_report.wh_lodge_calendar wlc on whs.house_id = wlc.house_id
    left join youjia_report.wh_house_calendar whc on wlc.house_id = whc.house_id and wlc.dt = whc.dt
    where wlc.dt between '{st}' and '{et}'
    and wlc.house_id = whc.house_id and wlc.dt = whc.dt
    and wlc.channel_id in {channel_ids}
    and whs.own_type in {own_type}
    and whs.work_name not like '%特殊门店'
    group by whs.own_type,whs.city_name
    with rollup;
    """.format(st=st, et=et, channel_ids=channel_ids, own_type=own_type)
    # df = pd.DataFrame([{"房屋ID": "", "渠道在线率": "", "渠道直连率": "", "离店订单数占总订单数比例": ""}])
    # df = df_util.df_set_index(df, '房屋ID')
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_channel(st, et, channel_ids=[3, 20, 25], own_type=(1, 2, 3)):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param channel_ids:
    :param own_type:
    :return:
    """
    return city_read_channel(st=st, et=et, channel_ids=channel_ids, own_type=own_type)


def city_read_brush(st, et, have_long: bool = False, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    刷单入住情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :param channel_ids:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'

    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    elif isinstance(own_type, list) and own_type.__len__() > 1:
        own_type = tuple(own_type)

    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)

    sql = """select 
            case group_concat(distinct whs.own_type order by whs.own_type)
                    when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            COALESCE(whs.city_name,'全国')  城市,
            count(distinct if(brush_channel in {channel_ids},brush_order_id,null)) 刷单订单量,
    
            round(sum(if(brush_channel in {channel_ids},brush_income,0))/sum(if(brush_channel in {channel_ids},brush_night,0))/100,2) 刷单ADR,
            sum(if(brush_channel in {channel_ids},brush_night,0))/ count(distinct if(brush_channel in {channel_ids},brush_order_id,null)) 刷单订单平均间夜量,
            concat(round(sum(if(order_channel in {channel_ids},have_order,0)+ if(brush_channel in {channel_ids},brush_night,0))/ sum(if(whc.online_stock=1 and whc.rent_stock = 1,1,0))*100,2),"%") 入住率（含刷单）
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name not like '%特殊门店'
            and whc.dt between '{st}' and '{et}'
            group by whs.own_type,whs.city_name
            with rollup;
    """.format(st=st, et=et, channel_ids=channel_ids, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_df_brush(st, et, have_long: bool = False, channel_ids=[3, 20, 25], own_type=(1, 2, 3)):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param channel_ids:
    :return:
    """
    return city_read_brush(st=st, et=et, have_long=have_long, channel_ids=channel_ids, own_type=own_type)


# todo  门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度

# todo  门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度

# todo  门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度

# todo  门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度↓↓↓门店维度


def work_read_related(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False, is_lase_year=False):
    """
    房源相关
    :param st:
    :param et:
    :param own_type:
    :param have_long:

    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
		-- case group_concat(distinct whs.own_type order by whs.own_type)
        --         when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
		if(COALESCE(whs.work_name,'全国') = '全国','全国',group_concat(distinct whs.city_name)) 城市,
        COALESCE(whs.work_name,'全国')  门店,
        if('{st}'='{et}','{st}',concat('{st}','-->','{et}')) 日期, 
        count(distinct if(date(whtn.online_time) between '{st}' and '{et}',whs.house_id,null)) 新增上线房源,
        count(distinct if(date(whtn.offline_time) between '{st}' and '{et}',whs.house_id,null)) 新增下线房源,
        -- count(distinct if( whs.house_state = 100,whs.house_id,null)) 当前在线房源,
        count(distinct if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),whs.house_id,null)) {lase_year}同期在线房屋,
        sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) 在线间夜,
        sum(if(whc.online_stock=1 and whc.rent_stock = 0,1,0)) 屏蔽间夜,
        concat(round(sum(if(whc.online_stock=1 and whc.rent_stock = 1,1,0))/sum(whc.online_stock)*100,2),"%") 可租率 
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_time_node whtn on whtn.house_id = whs.house_id
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whs.own_type in {own_type}
        {sql_add}
        and whc.dt between '{st}' and '{et}'
        and whs.work_name is not null
        group by whs.work_name
        with rollup
        
        """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_related(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    df = work_read_related(st=st, et=et, own_type=own_type, have_long=have_long)

    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    lase_year_df = work_read_related(st=last_st, et=last_et, own_type=own_type, have_long=have_long, is_lase_year=True)
    lase_year_df = lase_year_df[["城市", "门店", "去年同期在线房屋"]].copy()
    # return lase_year_df
    df = pd.merge(df, lase_year_df, how='left', on=["城市", "门店"])
    df = df_util.df_sort(df, '同期在线房屋')
    df = df[["城市", "门店", "日期", "新增上线房源", "新增下线房源", "去年同期在线房屋", "同期在线房屋", "在线间夜", "屏蔽间夜", "可租率"]]
    return df


def work_read_check(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False, is_lase_year=False):
    """
    房源相关
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:

    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
                if(COALESCE(A.门店,'全国') = '全国','全国',group_concat(distinct A.城市)) 城市,
                COALESCE(A.门店,'全国') 门店,
                sum(A.入住间夜) 入住间夜,
                concat(round(sum(A.入住间夜)/sum(A.可售间夜)*100,2),"%") {lase_year}可售入住率,
                concat(round(avg(A.月度可售入住率目标),2),"%") 月度可售入住率目标,
                concat(round(sum(A.入住间夜)/sum(A.可售间夜)*100-avg(A.月度可售入住率目标) ,2),"%") 可售入住率距月度目标差,
                sum(A.入住间夜)/sum(A.可售间夜) 可售入住率排名,
                concat(round(sum(A.入住间夜)/sum(A.在线间夜)*100,2),"%") 在线入住率,
                concat(round(sum(if(A.居室 =1, A.入住间夜,0))/sum(if(A.居室 =1, A.可售间夜,0))*100 ,2),"%") 一居可售入住率,
                concat(round(sum(if(A.居室 =2, A.入住间夜,0))/sum(if(A.居室 =2, A.可售间夜,0))*100 ,2),"%") 二居可售入住率,
                concat(round(sum(if(A.居室 =3, A.入住间夜,0))/sum(if(A.居室 =3, A.可售间夜,0))*100 ,2),"%") 三居可售入住率,
                concat(round(sum(if(A.居室 >3, A.入住间夜,0))/sum(if(A.居室 >3, A.可售间夜,0))*100 ,2),"%") 三居以上可售入住率
             from (select 
                    whs.house_id,
                    whs.own_type,
                    whs.room_num 居室,
                    whs.work_name 门店,
                    whs.city_name 城市,
                    whs.district_name 大区, 
                    sum(whc.have_order)  入住间夜,
                    round(target_check_rate/100,2)  月度可售入住率目标,
                    sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) 在线间夜,
                    sum(if((whc.online_stock = 1 and rent_stock = 1) or whc.have_order=1,1,0)) 可售间夜 
                    from youjia_report.wh_house_static whs
                    left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
                    where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
                    and whc.dt between '{st}' and '{et}'
                    and whs.own_type in {own_type}
                    {sql_add}
                    and whs.work_name not like '%特殊门店'
                    and whs.work_name is not null
                    group by whs.house_id
                    ) A
            group by A.门店
            with rollup
        """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_check(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return: 入住率
    可售房源入住率	在线房源入住率排名	在线房源入住率	去年同期在线房源入住率	在线房源入住率同期对比	在线房源入住率目标完成情况	一居室入住率	两居室入住率	三居室入住率	三居室+入住率

     cur_df = house_read_revpar(st=st, et=et, have_long=have_long, is_lase_year=False)
    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = house_read_revpar(st=last_st, et=last_et, have_long=have_long, is_lase_year=True)
    df = pd.merge(cur_df, last_df, how='left', on='房屋ID')
    """

    cur_df = work_read_check(st=st, et=et, own_type=own_type, have_long=have_long, is_lase_year=False)
    df_total = cur_df[cur_df.城市 == '全国'].copy()
    df_total["可售入住率排名"] = '-'

    df_other = cur_df[cur_df.城市 != '全国'].copy()
    df_other = df_util.df_set_index(df_other, ['城市', '门店'])
    df_other = df_util.df_sort(df_other, ['可售入住率排名'])
    df_other = df_util.df_reset_index(df_other)
    df_other['可售入住率排名'] = df_other.index + 1
    cur_df = pd.concat([df_total, df_other])

    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = work_read_check(st=last_st, et=last_et, have_long=have_long, is_lase_year=True)
    last_df = last_df[['城市', '门店', '去年可售入住率']]

    df = pd.merge(last_df, cur_df, how='right', on=['城市', '门店'])

    df['可售入住率同期对比'] = df.apply(lambda row: num_util.contrast(row, '可售入住率', '去年可售入住率'), axis=1)

    return df


def work_read_check_future14(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    房源相关
    :param st:
    :param et:
    :param own_type:
    :param have_long:

    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        -- case group_concat(distinct whs.own_type order by whs.own_type)
        --         when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
		if(COALESCE(whs.work_name,'全国') = '全国','全国',group_concat(distinct whs.city_name)) 城市,
        COALESCE(whs.work_name,'全国')  门店,
        concat(round(sum(whc.have_order) /sum(if((whc.online_stock = 1 and rent_stock = 1) or whc.have_order=1,1,0)) * 100,2),"%")  未来14天可售入住率
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whc.dt between '{st}' and '{et}'
        and whs.own_type in {own_type}
        {sql_add}
        and whs.work_name is not null
        group by whs.work_name
        with rollup
        """.format(st=st, et=et, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_check_future14(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return: 入住率
    """
    et = date_util.date_add(et, days=14)

    return work_read_check_future14(st=st, et=et, own_type=own_type, have_long=have_long)


def work_read_adr(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:
    :return:
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            -- case group_concat(distinct whs.own_type order by whs.own_type)
            --     when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(COALESCE(whs.work_name,'全国') = '全国','全国',group_concat(distinct whs.city_name)) 城市,
            COALESCE(whs.work_name,'全国')  门店,
            round(sum(ifnull(whc.airbnb_avg_price,0))/sum(if(whc.airbnb_avg_price<>0 and whc.airbnb_avg_price is not null ,1,0)),2)  市场价格,
            round(sum(income) / sum(whc.have_order)/100,2) {lase_year}ADR,
            round(target_adr/100,2) ADR同期对比差额,
            round((sum(income) / sum(whc.have_order) - target_adr)/100,2) ADR距月度目标差, 
            round(sum(if(whs.room_num=1,ifnull(income,0),0))/sum(if(whs.room_num=1,whc.have_order,0))/100,2)  一居ADR ,
            round(sum(if(whs.room_num=2,ifnull(income,0),0))/sum(if(whs.room_num=2,whc.have_order,0))/100,2)  二居ADR ,
            round(sum(if(whs.room_num=3,ifnull(income,0),0))/sum(if(whs.room_num=3,whc.have_order,0))/100,2)  三居ADR ,
            round(sum(if(whs.room_num>3,ifnull(income,0),0))/sum(if(whs.room_num>3,whc.have_order,0))/100,2)  三居以上ADR 
            
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt between '{st}' and '{et}'
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name is not null
            group by whs.work_name
            with rollup
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_adr(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    cur_df = work_read_adr(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = work_read_adr(st=last_st, et=last_et, have_long=have_long, is_lase_year=True, own_type=own_type)
    last_df = last_df[['城市', '门店', '去年ADR']]
    df = pd.merge(cur_df, last_df, how='left', on=['城市', '门店'])
    df['ADR同期对比差额'] = df.apply(lambda row: row['ADR'] - row['去年ADR'], axis=1)
    return df


def work_read_adr_future14(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            -- case group_concat(distinct whs.own_type order by whs.own_type)
            --     when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(COALESCE(whs.work_name,'全国') = '全国','全国',group_concat(distinct whs.city_name)) 城市,
            COALESCE(whs.work_name,'全国')  门店,
            round(sum(income) / sum(whc.have_order)/100,2) 未来14天ADR 
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt between '{st}' and '{et}'
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name is not null
            group by whs.work_name
            with rollup
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_adr_future14(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    et = date_util.date_add(et, days=14)
    return work_read_adr_future14(st=st, et=et, have_long=have_long, own_type=own_type)


def work_read_revpar(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:
    :return:
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            -- case group_concat(distinct whs.own_type order by whs.own_type)
            --    when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(COALESCE(whs.work_name,'全国') = '全国','全国',group_concat(distinct whs.city_name)) 城市,
            COALESCE(whs.work_name,'全国')  门店,
            round(sum(income+compensate_income)/sum(if((whc.online_stock=1 and whc.rent_stock=1) or whc.have_order = 1,1,0))/100,2) {lase_year}RevPar
            
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt between '{st}' and '{et}'
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name is not null
            group by whs.work_name
            with rollup
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_revpar(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    市场价格	revpar	RevPar	去年同期Revpar	RevPar同期比差额

    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    cur_df = work_read_revpar(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = work_read_revpar(st=last_st, et=last_et, have_long=have_long, is_lase_year=True, own_type=own_type)
    last_df = last_df[['城市', '门店', '去年RevPar']]
    df = pd.merge(cur_df, last_df, how='left', on=['城市', '门店'])
    df['RevPar同期对比差额'] = df.apply(lambda row: row['RevPar'] - row['去年RevPar'], axis=1)
    return df


def work_read_gmv(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    GMV	套均GMV	套均租金完成度排名	套均租金完成度	目标套均租金完成度
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:
    :return:
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select  A.城市,A.门店,A.GMV【不包含刷单】,A.套均GMV【不包含刷单】,
                    A.GMV目标,
                    A.GMV目标差,
                    A.套均租金完成度排名 ,
                    A.套均租金完成度
                from (select 
                -- case group_concat(distinct whs.own_type order by whs.own_type)
               --  when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
                if(COALESCE(whs.work_name,'全国') = '全国','全国',group_concat(distinct whs.city_name)) 城市,
                COALESCE(whs.work_name,'全国')  门店,
                round(sum(income+compensate_income)/100,2) GMV【不包含刷单】,
                round(sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)/100,2) 套均GMV【不包含刷单】,
                -- round(sum(income+compensate_income)/100/count(distinct whs.house_id),2) 套均GMV【不包含刷单】,
                round(avg(target_gmv)/100,2)  GMV目标,
                round((sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)-avg(target_gmv))/100,2) GMV目标差,
               --  sum(income+compensate_income)/count(distinct whs.house_id)/(sum(rent_day)/count(distinct whs.house_id)) 套均租金完成度排名, 
               --  concat(round(sum(income+compensate_income)/count(distinct whs.house_id)/(sum(rent_day)/count(distinct whs.house_id))*100,2),"%") 套均租金完成度
                sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)/(sum(rent_day)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)) 套均租金完成度排名, 
                concat(round(sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)/(sum(rent_day)/sum(whc.online_stock)*(datediff('{et}','{st}')+1))*100,2),"%") 套均租金完成度
    
                
                from youjia_report.wh_house_static whs
                left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
                where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
                and whc.dt between '{st}' and '{et}'
                and whs.own_type in {own_type}
                {sql_add}
                and whs.work_name is not null
                group by whs.work_name
                with rollup) A order by 套均租金完成度排名 desc 
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_gmv(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    GMV	套均GMV	套均租金完成度排名	套均租金完成度	目标套均租金完成度

    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    df = work_read_gmv(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    df['套均租金完成度排名'] = df.index + 1

    df_total = df[df.城市 == '全国'].copy()
    # df_total['套均租金完成度排名'] = '-'
    df_total['套均租金完成度排名'] = df_total.apply(lambda row: "-", axis=1)

    df_other = df[df.城市 != '全国'].copy()
    df_other = df_util.df_set_index(df_other, ['城市', '门店'])
    df_other = df_util.df_sort(df_other, ['套均租金完成度排名'], ascending=True)
    df_other = df_util.df_reset_index(df_other)
    df_other['套均租金完成度排名'] = df_other.index + 1
    df = pd.concat([df_total, df_other], sort=False)
    return df


def work_read_create_orders_num(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """

    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)

    sql = """select 
            -- case group_concat(distinct whs.own_type order by whs.own_type)
            --    when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(COALESCE(whs.work_name,'全国') = '全国','全国',group_concat(distinct whs.city_name)) 城市,
            COALESCE(whs.work_name,'全国')  门店,
            round(sum(whc.compensate_income)/100,2) 取消订单收入
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whs.own_type in {own_type}
            and whc.dt between '{st}' and '{et}'
            {sql_add}
            and whs.work_name is not null
            group by whs.work_name
            with rollup
        """.format(st=st, et=et, sql_add=sql_add, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_create_orders_num(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """

    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    df = work_read_create_orders_num(st=st, et=et, own_type=own_type, have_long=have_long)
    work_refund_rate_df = work_refund_rate(st=st, et=et, own_type=own_type, have_long=have_long, is_int=False)
    work_refund_rate_df = df_util.df_reset_index(work_refund_rate_df)
    df = pd.merge(df, work_refund_rate_df, how='left')

    return df


def work_read_channel_order_rate(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    离店订单占比-airbnb	离店订单占比-途家	离店订单占比-榛果	离店订单占比-其他
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    sql = """select 
    -- case group_concat(distinct whs.own_type order by whs.own_type)
    --             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
    if(COALESCE(whs.work_name,'全国') = '全国','全国',group_concat(distinct whs.city_name)) 城市,
    COALESCE(whs.work_name,'全国')  门店,
    concat(round(ifnull(count(distinct if(order_channel = 20,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-Airbnb`,
    concat(round(ifnull(count(distinct if(order_channel = 3,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-途家`,
    concat(round(ifnull(count(distinct if(order_channel = 25,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-榛果`,
    concat(round(ifnull(count(distinct if(order_channel not in (3,20,25),order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-其他` 
    from youjia_report.wh_house_static whs
    left join youjia_report.wh_house_calendar whc on whs.house_id = whc.house_id
    where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
    and whs.own_type in {own_type}
    and whc.dt between '{st}' and '{et}'
    {sql_add}
    and whs.work_name is not null
    group by whs.work_name
    with rollup""".format(st=st, et=et, sql_add=sql_add, own_type=own_type)

    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_channel_order_rate(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    渠道占比
    离店订单占比-airbnb	离店订单占比-途家	离店订单占比-榛果	离店订单占比-其他
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    return work_read_channel_order_rate(st=st, et=et, have_long=have_long, own_type=own_type)


def work_read_net_profit(st, et, own_type=(1, 2, 3)):
    """
    净利（非结算数据）
    净利-公司	套均净利-公司	净利-投资人	套均净利-投资人

    :param st:
    :param et:
    :param own_type:
    :return:
    """
    df = pd.DataFrame([{"城市": "全国", "门店": "全国", "净利-公司": "",
                        "套均净利-公司": "", "净利-投资人": "", "套均净利-投资人": ""}])
    df = df_util.df_set_index(df, ['城市', '门店'])
    return df


def work_df_net_profit(st, et, own_type=(1, 2, 3)):
    """
    电话接起率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    return work_read_net_profit(st=st, et=et, own_type=own_type)


def work_read_channel(st, et, own_type: tuple = (1, 2, 3), channel_ids: list = [3, 20, 25]):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)
    sql = """SELECT 
	-- case group_concat(distinct whs.own_type order by whs.own_type)
    --            when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
	if(COALESCE(whs.work_name,'全国') = '全国','全国',group_concat(distinct whs.city_name)) 城市,
	COALESCE(whs.work_name,'全国')  门店,
    concat(round(ifnull(sum(if(wlc.is_distribution = 1,ifnull(wlc.third_state,0),0))/sum(ifnull(wlc.is_distribution,0)),0)*100,2),"%") 直连在线率,
    concat(round(ifnull(sum(ifnull(wlc.is_distribution,0))/sum(whc.online_stock),0)*100,2),"%") 渠道直连率
    
    FROM youjia_report.wh_house_static whs 
    left join youjia_report.wh_lodge_calendar wlc on whs.house_id = wlc.house_id
    left join youjia_report.wh_house_calendar whc on wlc.house_id = whc.house_id and wlc.dt = whc.dt
    where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
    and wlc.dt between '{st}' and '{et}'
    and wlc.house_id = whc.house_id and wlc.dt = whc.dt
    and wlc.channel_id in {channel_ids}
    and whs.own_type in {own_type}
    and whs.work_name is not null
    group by whs.work_name
    with rollup;
    """.format(st=st, et=et, channel_ids=channel_ids, own_type=own_type)
    # df = pd.DataFrame([{"房屋ID": "", "渠道在线率": "", "渠道直连率": "", "离店订单数占总订单数比例": ""}])
    # df = df_util.df_set_index(df, '房屋ID')
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_channel(st, et, channel_ids=[3, 20, 25], own_type=(1, 2, 3)):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param channel_ids:
    :param own_type:
    :return:
    """
    return work_read_channel(st=st, et=et, channel_ids=channel_ids, own_type=own_type)


def work_read_brush(st, et, have_long: bool = False, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    刷单入住情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :param channel_ids:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'

    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    elif isinstance(own_type, list) and own_type.__len__() > 1:
        own_type = tuple(own_type)

    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)
    sql = """select 
            -- case group_concat(distinct whs.own_type order by whs.own_type)
            --    when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(COALESCE(whs.work_name,'全国') = '全国','全国',group_concat(distinct whs.city_name)) 城市,
            COALESCE(whs.work_name,'全国')  门店,
            count(distinct if(brush_channel in {channel_ids},brush_order_id,null)) 刷单订单量,

            round(sum(if(brush_channel in {channel_ids},brush_income,0))/sum(if(brush_channel in {channel_ids},brush_night,0))/100,2) 刷单ADR,
            sum(if(brush_channel in {channel_ids},brush_night,0))/ count(distinct if(brush_channel in {channel_ids},brush_order_id,null)) 刷单订单平均间夜量,
            concat(round(sum(if(order_channel in {channel_ids},have_order,0)+ if(brush_channel in {channel_ids},brush_night,0))/ sum(if(whc.online_stock=1 and whc.rent_stock = 1,1,0))*100,2),"%") 入住率（含刷单）
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whs.own_type in {own_type}
            {sql_add}
            and whc.dt between '{st}' and '{et}'
            and whs.work_name is not null
            group by whs.work_name
            with rollup;
        """.format(st=st, et=et, channel_ids=channel_ids, own_type=own_type, sql_add=sql_add)

    return pd.read_sql_query(sql, db_util.con_youjia)


def work_df_brush(st, et, have_long: bool = False, channel_ids=[3, 20, 25], own_type=(1, 2, 3)):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param channel_ids:
    :return:
    """
    return work_read_brush(st=st, et=et, have_long=have_long, channel_ids=channel_ids, own_type=own_type)


# todo  大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度

# todo  大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度

# todo  大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度

# todo  大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度↓↓↓大区维度

def district_read_related(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False, is_lase_year=False):
    """
    房源相关
    :param st:
    :param et:
    :param own_type:
    :param have_long:

    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    lase_year = '去年' if is_lase_year else ''
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        COALESCE(whs.district_name,'全国')  大区,
		if(COALESCE(whs.district_name,'全国') = '全国','全国',group_concat(distinct whs.city_name order by whs.city_name)) 城市,
        if('{st}'='{et}','{st}',concat('{st}','-->','{et}')) 日期, 
        count(distinct if(date(whtn.online_time) between '{st}' and '{et}',whs.house_id,null)) 新增上线房屋,
        count(distinct if(date(whtn.offline_time) between '{st}' and '{et}',whs.house_id,null)) 新增下线房屋,
        -- count(distinct if(whs.house_state = 100,whs.house_id,null)) 当前在线房屋,
        count(distinct if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),whs.house_id,null)) {lase_year}同期在线房屋,
        sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) 在线间夜,
        -- sum(whc.online_stock) 在线间夜,
        sum(if(whc.online_stock=1 and whc.rent_stock = 0,1,0)) 屏蔽间夜,
        concat(round(sum(if(whc.online_stock=1 and whc.rent_stock = 1,1,0))/sum(whc.online_stock)*100,2),"%") 可售率 
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_time_node whtn on whtn.house_id = whs.house_id
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whs.own_type in {own_type}
        {sql_add}
        and whc.dt between '{st}' and '{et}'
        and whs.work_name not like '%特殊门店'
        group by whs.district_name
        with rollup
        """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_related(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    df = district_read_related(st=st, et=et, own_type=own_type, have_long=have_long)

    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    lase_year_df = district_read_related(st=last_st, et=last_et, own_type=own_type, have_long=have_long,
                                         is_lase_year=True)
    lase_year_df = lase_year_df[["大区", "去年同期在线房屋"]]
    df = pd.merge(df, lase_year_df, how='left', on=["大区"])
    df = df_util.df_sort(df, '同期在线房屋')
    # df = df[["城市", "公司", "日期", "新增上线房屋", "新增下线房屋", "去年同期在线房屋", "同期在线房屋", "在线间夜", "屏蔽间夜", "可售率"]]
    return df


def district_read_check(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False, is_lase_year=False):
    """
    房源相关
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:

    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select COALESCE(A.大区,'全国')  大区,
            sum(A.入住间夜) 入住间夜,
            concat(round(sum(A.入住间夜)/sum(A.可售间夜)*100,2),"%") {lase_year}可售入住率,
            concat(round(avg(A.月度可售入住率目标),2),"%") 月度可售入住率目标,
            concat(round(sum(A.入住间夜)/sum(A.可售间夜)*100-avg(A.月度可售入住率目标) ,2),"%") 可售入住率距月度目标差,
			sum(A.入住间夜)/sum(A.可售间夜) 可售入住率排名,
			concat(round(sum(A.入住间夜)/sum(A.在线间夜)*100,2),"%") 在线入住率,
            concat(round(sum(if(A.居室 =1, A.入住间夜,0))/sum(if(A.居室 =1, A.可售间夜,0))*100 ,2),"%") 一居可售入住率,
            concat(round(sum(if(A.居室 =2, A.入住间夜,0))/sum(if(A.居室 =2, A.可售间夜,0))*100 ,2),"%") 二居可售入住率,
            concat(round(sum(if(A.居室 =3, A.入住间夜,0))/sum(if(A.居室 =3, A.可售间夜,0))*100 ,2),"%") 三居可售入住率,
            concat(round(sum(if(A.居室 >3, A.入住间夜,0))/sum(if(A.居室 >3, A.可售间夜,0))*100 ,2),"%") 三居以上可售入住率
            
             from ( select 
                    whs.house_id,
                    whs.own_type,
                    whs.room_num 居室,
                    whs.city_name 城市,
                    whs.district_name 大区, 
                    sum(whc.have_order)  入住间夜,
                    round(target_check_rate/100,2)  月度可售入住率目标,
                    sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) 在线间夜,
                    sum(if((whc.online_stock = 1 and rent_stock = 1) or whc.have_order=1,1,0)) 可售间夜 
                    from youjia_report.wh_house_static whs
                    left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
                    where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
                    and whc.dt between '{st}' and '{et}'
                    and whs.own_type in {own_type}
                    {sql_add}
                    and whs.work_name not like '%特殊门店'
                    group by whs.house_id
                    ) A
            group by 大区
            with rollup
        """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_check(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return: 入住率
    """

    cur_df = district_read_check(st=st, et=et, own_type=own_type, have_long=have_long, is_lase_year=False)
    df_total = cur_df[cur_df.大区 == '全国'].copy()
    # df_total['可售入住率排名'] = '-'
    df_total['可售入住率排名'] = df_total.apply(lambda row: "-", axis=1)

    df_other = cur_df[cur_df.大区 != '全国'].copy()
    df_other = df_util.df_set_index(df_other, ['大区'])
    df_other = df_util.df_sort(df_other, ['可售入住率排名'])
    df_other = df_util.df_reset_index(df_other)
    df_other['可售入住率排名'] = df_other.index + 1
    cur_df = pd.concat([df_total, df_other])

    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = district_read_check(st=last_st, et=last_et, have_long=have_long, is_lase_year=True)
    last_df = last_df[['大区', '去年可售入住率']]

    df = pd.merge(last_df, cur_df, how='right', on=['大区'])

    df['可售入住率同期对比'] = df.apply(lambda row: num_util.contrast(row, '可售入住率', '去年可售入住率'), axis=1)

    return df


def district_read_check_future14(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False, is_lase_year=False):
    """
    房源相关
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:

    :return: 上线时间 房型 在线天数 屏蔽天数 可租率
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        COALESCE(whs.district_name,'全国')  大区,
		-- if(COALESCE(whs.district_name,'全国') = '全国','全国',group_concat(distinct whs.city_name order by whs.city_name)) 城市,
        concat(round(sum(whc.have_order) /sum(if((whc.online_stock = 1 and rent_stock = 1) or whc.have_order=1,1,0)) * 100,2),"%")  未来14天可售入住率
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        and whc.dt between '{st}' and '{et}'
        and whs.own_type in {own_type}
        {sql_add}
        and whs.work_name not like '%特殊门店'
        group by district_name
        with rollup
        """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_check_future14(st, et, own_type: tuple = (1, 2, 3), have_long: bool = False):
    """
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return: 入住率
    """
    et = date_util.date_add(et, days=14)

    return district_read_check_future14(st=st, et=et, own_type=own_type, have_long=have_long, is_lase_year=False)


def district_read_adr(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :param is_lase_year:
    :return:
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            COALESCE(whs.district_name,'全国')  大区,
		    -- if(COALESCE(whs.district_name,'全国') = '全国','全国',group_concat(distinct whs.city_name order by whs.city_name)) 城市,
            round(sum(ifnull(whc.airbnb_avg_price,0))/sum(if(whc.airbnb_avg_price<>0 and whc.airbnb_avg_price is not null ,1,0)),2) 市场价格,
            round(sum(income) / sum(whc.have_order)/100,2) {lase_year}ADR,
            '' ADR同期对比差额,
            round(target_adr/100,2) 月度ADR目标,
            round((sum(income) / sum(whc.have_order) - target_adr)/100,2) ADR距月度目标差,
            round(sum(if(whs.room_num=1,ifnull(income,0),0))/sum(if(whs.room_num=1,whc.have_order,0))/100,2)  一居ADR ,
            round(sum(if(whs.room_num=2,ifnull(income,0),0))/sum(if(whs.room_num=2,whc.have_order,0))/100,2)  二居ADR ,
            round(sum(if(whs.room_num=3,ifnull(income,0),0))/sum(if(whs.room_num=3,whc.have_order,0))/100,2)  三居ADR ,
            round(sum(if(whs.room_num>3,ifnull(income,0),0))/sum(if(whs.room_num>3,whc.have_order,0))/100,2)  三居以上ADR 

            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt between '{st}' and '{et}'
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name not like '%特殊门店'
            group by district_name
            with rollup
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_adr(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    cur_df = district_read_adr(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = district_read_adr(st=last_st, et=last_et, have_long=have_long, is_lase_year=True, own_type=own_type)
    last_df = last_df[['大区', '去年ADR']]
    df = pd.merge(cur_df, last_df, how='left', on=['大区'])
    df['ADR同期对比差额'] = df.apply(lambda row: row['ADR'] - row['去年ADR'], axis=1)
    return df


def district_read_adr_future14(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            COALESCE(whs.district_name,'全国')  大区,
		    -- if(COALESCE(whs.district_name,'全国') = '全国','全国',group_concat(distinct whs.city_name order by whs.city_name)) 城市,
            round(sum(income) / sum(whc.have_order)/100,2) 未来14天平均ADR 
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt between '{st}' and '{et}'
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name not like '%特殊门店'
            group by district_name
            with rollup
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_adr_future14(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :return:
    """
    et = date_util.date_add(days=14, dt=et)
    return district_read_adr_future14(st=st, et=et, have_long=have_long, own_type=own_type)


def district_read_revpar(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    市场价格	ADR	去年同期ADR	ADR同期对比差额	ADR目标完成情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :param is_lase_year:
    :return:
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            COALESCE(whs.district_name,'全国')  大区,
		    -- if(COALESCE(whs.district_name,'全国') = '全国','全国',group_concat(distinct whs.city_name order by whs.city_name)) 城市,
            round(sum(income+compensate_income)/sum(if((whc.online_stock=1 and whc.rent_stock=1) or whc.have_order = 1,1,0))/100,2) {lase_year}RevPar

            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt between '{st}' and '{et}'
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name not like '%特殊门店'
            group by whs.district_name
            with rollup
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_revpar(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    市场价格	revpar	RevPar	去年同期Revpar	RevPar同期比差额

    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    cur_df = district_read_revpar(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    last_st = date_util.date_sub(st, days=0, years=1)
    last_et = date_util.date_sub(et, days=0, years=1)
    last_df = district_read_revpar(st=last_st, et=last_et, have_long=have_long, is_lase_year=True, own_type=own_type)
    last_df = last_df[['大区', '去年RevPar']]
    df = pd.merge(cur_df, last_df, how='left', on=['大区'])
    df['RevPar同期对比差额'] = df.apply(lambda row: row['RevPar'] - row['去年RevPar'], axis=1)
    return df


def district_read_gmv(st, et, own_type=(1, 2, 3), have_long: bool = False, is_lase_year: bool = False):
    """
    GMV	套均GMV	套均租金完成度排名	套均租金完成度	目标套均租金完成度
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_lase_year:
    :return:
    """
    lase_year = '去年' if is_lase_year else ''
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select A.大区, A.GMV【不包含刷单】,A.套均GMV【不包含刷单】,A.GMV目标,A.GMV目标差,A.套均租金完成度排名,A.套均租金完成度
                from (select 
                COALESCE(whs.district_name,'全国')  大区,
		        -- if(COALESCE(whs.district_name,'全国') = '全国','全国',group_concat(distinct whs.city_name order by whs.city_name)) 城市,
                round(sum(income+compensate_income+other_income)/100,2) GMV【不包含刷单】,
                round(sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)/100,2) 套均GMV【不包含刷单】,
                round(avg(target_gmv)/100,2)  GMV目标,
                round((sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)-avg(target_gmv))/100,2) GMV目标差,
                sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)/(sum(rent_day)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)) 套均租金完成度排名, 
                concat(round(sum(income+compensate_income+other_income)/sum(whc.online_stock)*(datediff('{et}','{st}')+1)/(sum(rent_day)/sum(whc.online_stock)*(datediff('{et}','{st}')+1))*100,2),"%") 套均租金完成度
                -- concat(round(sum(income+compensate_income)/count(distinct whs.house_id)/(sum(rent_day)/count(distinct whs.house_id))*100,2),"%") 套均租金完成度


                from youjia_report.wh_house_static whs
                left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
                where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
                and whc.dt between '{st}' and '{et}'
                and whs.own_type in {own_type}
                {sql_add}
                and whs.work_name not like '%特殊门店'
                group by whs.district_name
                with rollup) A order by 套均租金完成度排名 desc 
           """.format(st=st, et=et, own_type=own_type, sql_add=sql_add, lase_year=lase_year)
    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_gmv(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    GMV	套均GMV	套均租金完成度排名	套均租金完成度	目标套均租金完成度

    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    df = district_read_gmv(st=st, et=et, have_long=have_long, is_lase_year=False, own_type=own_type)
    df['套均租金完成度排名'] = df.index + 1

    df_total = df[df.大区 == '全国'].copy()
    # df_total['套均租金完成度排名'] = 0
    df_total['套均租金完成度排名'] = df_total.apply(lambda row: '-', axis=1)

    df_other = df[df.大区 != '全国'].copy()
    df_other = df_util.df_set_index(df_other, ['大区'])
    df_other = df_util.df_sort(df_other, ['套均租金完成度排名'], ascending=True)
    df_other = df_util.df_reset_index(df_other)
    df_other['套均租金完成度排名'] = df_other.index + 1
    df = pd.concat([df_total, df_other], sort=False)
    return df


def district_read_create_orders_num(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """

    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'

    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)

    sql = """select 
             COALESCE(whs.district_name,'全国')  大区,
		     -- if(COALESCE(whs.district_name,'全国') = '全国','全国',group_concat(distinct whs.city_name order by whs.city_name)) 城市,
             round(sum(whc.compensate_income)/100,2) 取消订单收入
             from youjia_report.wh_house_static whs
             left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
             where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
             and whc.dt between '{st}' and '{et}'
             and whs.own_type in {own_type}
             {sql_add}
             and whs.work_name not like '%特殊门店'
             group by whs.district_name
             with rollup
        """.format(st=st, et=et, sql_add=sql_add, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_create_orders_num(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """

    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    df = district_read_create_orders_num(st=st, et=et, have_long=have_long)
    district_refund_rate_df = district_refund_rate(st=st, et=et, own_type=own_type, is_int=False)
    district_refund_rate_df = df_util.df_reset_index(district_refund_rate_df)
    df = pd.merge(df, district_refund_rate_df, how='left', on=['大区'])
    df = df[['大区', '取消订单收入', '退款率']]

    return df


def district_read_net_profit(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """

    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """
    sql_add = "" if have_long else 'and bo.order_type <> 3'

    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    net_profits = [{"大区": "-", "净利-公司": "-", "套均净利-公司": "-", "净利-投资人": "-", "套均净利-投资人": "-"}]

    return pd.DataFrame(net_profits)


def district_df_net_profit(st, et, own_type=(1, 2, 3), have_long: bool = False):
    """
        净利
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :return:
    """

    return district_read_net_profit(st=st, et=et, own_type=own_type, have_long=have_long)


def district_read_channel_order_rate(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    离店订单占比-airbnb	离店订单占比-途家	离店订单占比-榛果	离店订单占比-其他
    :param st:
    :param et:
    :param have_long:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
    COALESCE(whs.district_name,'全国')  大区,
	-- if(COALESCE(whs.district_name,'全国') = '全国','全国',group_concat(distinct whs.city_name order by whs.city_name)) 城市,
    concat(round(ifnull(count(distinct if(order_channel = 20,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-Airbnb`,
    concat(round(ifnull(count(distinct if(order_channel = 3,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-途家`,
    concat(round(ifnull(count(distinct if(order_channel = 25,order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-榛果`,
    concat(round(ifnull(count(distinct if(order_channel not in (3,20,25),order_id,null))/count(distinct order_id)*100,0),2),"%") `离店订单占比-其他` 
    from youjia_report.wh_house_static whs
    left join youjia_report.wh_house_calendar whc on whs.house_id = whc.house_id
    where  whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
     and whc.dt between '{st}' and '{et}'
     and whs.own_type in {own_type}
    {sql_add}
    and whs.work_name not like '%特殊门店'
    group by whs.district_name
    with rollup""".format(st=st, et=et, sql_add=sql_add, own_type=own_type)

    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_channel_order_rate(st, et, have_long: bool = False, own_type=(1, 2, 3)):
    """
    渠道占比
    离店订单占比-airbnb	离店订单占比-途家	离店订单占比-榛果	离店订单占比-其他
    :param st:
    :param et:
    :param have_long:
    :return:
    """
    return district_read_channel_order_rate(st=st, et=et, have_long=have_long, own_type=own_type)


def district_read_phone(st, et):
    """
    电话接起率
    :param st:
    :param et:
    :return:
    """
    df = pd.DataFrame([{"大区": "全国", "来电次数": "", "接起电话次数": "", "电话接起率": ""}])
    df = df_util.df_set_index(df, ['大区'])
    return df


def district_df_phone(st, et):
    """
    电话接起率
    :param st:
    :param et:
    :return:
    """
    return district_read_phone(st=st, et=et)


def district_read_channel(st, et, own_type: tuple = (1, 2, 3), channel_ids: list = [3, 20, 25]):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)
    sql = """SELECT 
	COALESCE(whs.district_name,'全国')  大区,
	-- if(COALESCE(whs.district_name,'全国') = '全国','全国',group_concat(distinct whs.city_name order by whs.city_name)) 城市,
    concat(round(ifnull(sum(if(wlc.is_distribution = 1,ifnull(wlc.third_state,0),0))/sum(ifnull(wlc.is_distribution,0)),0)*100,2),"%") 直连在线率,
    concat(round(ifnull(sum(ifnull(wlc.is_distribution,0))/sum(whc.online_stock),0)*100,2),"%") 渠道直连率 

    FROM youjia_report.wh_house_static whs 
    left join youjia_report.wh_lodge_calendar wlc on whs.house_id = wlc.house_id
    left join youjia_report.wh_house_calendar whc on wlc.house_id = whc.house_id and wlc.dt = whc.dt
    where wlc.dt between '{st}' and '{et}'
    and wlc.house_id = whc.house_id and wlc.dt = whc.dt
    and wlc.channel_id in {channel_ids}
    and whs.own_type in {own_type}
    and whs.work_name not like '%特殊门店'
    group by whs.district_name
    with rollup;
    """.format(st=st, et=et, channel_ids=channel_ids, own_type=own_type)
    # df = pd.DataFrame([{"房屋ID": "", "渠道在线率": "", "渠道直连率": "", "离店订单数占总订单数比例": ""}])
    # df = df_util.df_set_index(df, '房屋ID')
    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_channel(st, et, channel_ids=[3, 20, 25], own_type=(1, 2, 3)):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param channel_ids:
    :param own_type:
    :return:
    """
    return district_read_channel(st=st, et=et, channel_ids=channel_ids, own_type=own_type)


def district_read_brush(st, et, have_long: bool = False, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    刷单入住情况
    :param st:
    :param et:
    :param have_long:
    :param own_type:
    :param channel_ids:
    :return:
    """
    sql_add = "" if have_long else 'and whc.order_type <> 3'

    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    elif isinstance(own_type, list) and own_type.__len__() > 1:
        own_type = tuple(own_type)

    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)

    sql = """select 
            COALESCE(whs.district_name,'全国')  大区,
		    -- if(COALESCE(whs.district_name,'全国') = '全国','全国',group_concat(distinct whs.city_name order by whs.city_name)) 城市,
            count(distinct if(brush_channel in {channel_ids},brush_order_id,null)) 刷单订单量,

            round(sum(if(brush_channel in {channel_ids},brush_income,0))/sum(if(brush_channel in {channel_ids},brush_night,0))/100,2) 刷单ADR,
            sum(if(brush_channel in {channel_ids},brush_night,0))/ count(distinct if(brush_channel in {channel_ids},brush_order_id,null)) 刷单订单平均间夜量,
            concat(round(sum(if(order_channel in {channel_ids},have_order,0)+ if(brush_channel in {channel_ids},brush_night,0))/ sum(if(whc.online_stock=1 and whc.rent_stock = 1,1,0))*100,2),"%") 入住率（含刷单）
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whs.own_type in {own_type}
            {sql_add}
            and whs.work_name not like '%特殊门店'
            and whc.dt between '{st}' and '{et}'
            group by whs.district_name
            with rollup;
    """.format(st=st, et=et, channel_ids=channel_ids, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.con_youjia)


def district_df_brush(st, et, have_long: bool = False, channel_ids=[3, 20, 25], own_type=(1, 2, 3)):
    """
    渠道在线率 渠道直连率
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param channel_ids:
    :return:
    """
    return district_read_brush(st=st, et=et, have_long=have_long, channel_ids=channel_ids, own_type=own_type)


if __name__ == '__main__':
    pass
    main_st = '2019-12-01'
    main_et = '2019-12-10'
    # mul_im_recovery_df = im_house_day(st=st, et=et, channels=[3, 20, 25])
    # mul_im_conversion_df = im_conversion_order_house(st=st, et=et, channels=[3, 20, 25])
    # mul_im_df = pd.merge(mul_im_recovery_df, mul_im_conversion_df, how='outer', left_index=True, right_index=True)
    # brush_house_df = month_order_house(st=st, et=et)

    # city_dict, color_dict = city_df_create_orders_num(dt=et)
    # excel_util.pd_to_excel(city_dict, "x", engine='xlsxwriter', merge_cells=True)
    # excel_util.to_excel_style(city_dict, "y", colors_dict=color_dict, engine='xlsxwriter', merge_cells=True)

    # df = process_work(st, et)
    main_df = work_df_related(main_st, main_et)
    excel_util.to_excel_style({"df": main_df }, "y", engine='xlsxwriter', merge_cells=True, freeze_panes=(3, 3))
